package com.thinkit.bigdata.web.service.impl.bus;

import com.google.common.base.Strings;
import com.thinkit.bigdata.core.ConfigHelper;
import com.thinkit.bigdata.core.feature.orm.mybatis.Page;
import com.thinkit.bigdata.core.util.*;
import com.thinkit.bigdata.core.util.es.ESUtil;
import com.thinkit.bigdata.core.util.excel.LocalExcelUtils;
import com.thinkit.bigdata.core.util.httpUtil.HttpUtil;
import com.thinkit.bigdata.core.util.ip.IPUtil;
import com.thinkit.bigdata.core.util.thread.ThreadExecutorUtil;
import com.thinkit.bigdata.web.controller.pubmet.PubMethod;
import com.thinkit.bigdata.web.coremet.SystemStatic;
import com.thinkit.bigdata.web.dao.bus.BusVoiceDetailsMapper;
import com.thinkit.bigdata.web.dao.sec.SysUserMapper;
import com.thinkit.bigdata.web.model.BaseEntity;
import com.thinkit.bigdata.web.model.bus.BusVoiceDetails;
import com.thinkit.bigdata.web.model.bus.BusVoiceDetailsExample;
import com.thinkit.bigdata.web.model.bus.BusVoiceDetailsSearch;
import com.thinkit.bigdata.web.model.bus.JsonBeanPamar;
import com.thinkit.bigdata.web.model.config.SysArea;
import com.thinkit.bigdata.web.model.es.audioEs.Appeal;
import com.thinkit.bigdata.web.model.es.audioEs.BusinessTags;
import com.thinkit.bigdata.web.model.es.audioEs.Source;
import com.thinkit.bigdata.web.model.es.audioEs.VoiceInfo;
import com.thinkit.bigdata.web.model.result.Constants;
import com.thinkit.bigdata.web.model.result.HtmlResult;
import com.thinkit.bigdata.web.model.sec.User;
import com.thinkit.bigdata.web.model.sec.UserExample;
import com.thinkit.bigdata.web.service.bus.BusVoiceDetailsService;
import com.thinkit.bigdata.web.service.config.AreaService;
import com.thinkit.bigdata.web.service.sec.GroupService;
import com.thinkit.bigdata.web.service.sys.SysConfigService;
import com.thinkit.bigdata.web.thread.Producer;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.apache.lucene.search.join.ScoreMode;
import org.apache.shiro.SecurityUtils;
import org.elasticsearch.index.query.BoolQueryBuilder;
import org.elasticsearch.index.query.QueryBuilders;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.io.File;
import java.io.IOException;
import java.util.*;
import java.util.stream.Collectors;
import java.util.stream.Stream;

@Service
public class BusVoiceDetailsServiceImpl implements BusVoiceDetailsService {

    private static Logger logger = Logger.getLogger(BusVoiceDetailsServiceImpl.class);

    @Resource
    protected SysUserMapper userMapper;

    @Resource
    private BusVoiceDetailsMapper detailsMapper;

    @Resource
    private AreaService areaService;

    @Resource
    private GroupService groupService;

    @Resource
    private ESUtil esUtil;

    @Resource
    private ProjectPubMet pubMet;

    @Resource
    private SysConfigService sysConfigService;

    @Override
    public HtmlResult voiceListByPessim(String provinceCode, String dataCode) {
        // 获取当前登录用户信息
        User userInfo = (User) SecurityUtils.getSubject().getSession().getAttribute(Constants.SESSION_USER_INFO);
        BusVoiceDetailsExample example = new BusVoiceDetailsExample();
        BusVoiceDetailsExample.Criteria criteria = example.createCriteria();
        criteria.andCreateuseridEqualTo(userInfo.getId());
        if (!Strings.isNullOrEmpty(provinceCode)) {
            criteria.andProvincecodeEqualTo(provinceCode);
        }
        criteria.andDatacodeEqualTo(dataCode);
        return HtmlResult.success(detailsMapper.selectByExample(example));
    }

    @Override
    public HtmlResult insert(BusVoiceDetails detail) {
        JsonBeanPamar param = detail.getParams();
        BoolQueryBuilder boolQuery = new BoolQueryBuilder();
        formatQ(boolQuery, param, detail.getDatacode(), "wildcard");
        //总条数
        Integer sum = SystemStatic.crud.queryQBuilderAllByMes(SystemStatic.AUDIO_ES_INDEX, SystemStatic.AUDIO_ES_TYPE, boolQuery);
        if (sum == 0) {
            return HtmlResult.failure("没有满足条件的数据");
        }
        if (!Strings.isNullOrEmpty(param.getProvincecode())) {
            param.setProvince(areaService.selectByPrimaryCode(param.getProvincecode()).getArea());
        }
        if (!Strings.isNullOrEmpty(param.getGroupid())) {
            String[] ids = param.getGroupid().split(",");
            StringBuilder names = new StringBuilder();
            for (int i = 0; i < ids.length; i++) {
                if (i == ids.length - 1) {
                    names.append(groupService.selectByPrimaryKey(Long.parseLong(ids[i])).getName());
                } else {
                    names.append(groupService.selectByPrimaryKey(Long.parseLong(ids[i])).getName()).append(",");
                }
            }
            param.setSeatgroup(names.toString());
        }
        if (!Strings.isNullOrEmpty(param.getSeatid())) {
            String[] ids = param.getSeatid().split(",");
            StringBuilder names = new StringBuilder();
            for (int i = 0; i < ids.length; i++) {
                UserExample example = new UserExample();
                example.createCriteria().andAccountEqualTo(ids[i]);
                List<User> userList = userMapper.selectByExample(example);
                if (!userList.isEmpty()) {
                    if (i == ids.length - 1) {
                        names.append(userList.get(0).getRealname());
                    } else {
                        names.append(userList.get(0).getRealname()).append(",");
                    }
                }
            }
            param.setSeatname(names.toString());
        }
        long now = DateFormatUtil.getDataTime();
        User userInfo = (User) SecurityUtils.getSubject().getSession().getAttribute(Constants.SESSION_USER_INFO);
        detail.setCreatetime(new Date());
        detail.setCreateuserid(userInfo.getId());
        detail.setCreateuser(userInfo.getUsername());
        SysArea area = areaService.selectAreaByUserId(userInfo.getId());
        if (null != area) {
            detail.setProvincecode(area.getCode());
            detail.setProvince(area.getArea());
        }
        detail.setStatus(BaseEntity.VOICE_EXPORT_BEGIN);
        detail.setFilename(userInfo.getId() + "_" + now + ".xls");
        detail.setFilepath(SystemStatic.SYSTEM_USER_PATH_EXPORT + detail.getFilename());
        detail.setIsdel(BaseEntity.FILE_IS_DEL_NO);
        detail.setLocalip(IPUtil.getLocalIP());
        if (detailsMapper.insertSelective(detail) == 0) {
            return HtmlResult.failure("添加失败");
        }
        //将任务放到阻塞队列中
        ThreadExecutorUtil.executeTask(new Producer(SystemStatic.queue, detail));
        return HtmlResult.success("添加成功");
    }

    @Override
    public HtmlResult insert5G(String dataCode, String provinceCode, String callTags, String scene, String callStartTime, String callEndTime) {
        JsonBeanPamar param = new JsonBeanPamar();
        param.setCallstarttime(callStartTime);
        param.setCallendtime(callEndTime);
        param.setProvincecode(provinceCode);
        param.setCalltags(callTags);
        param.setScene(scene);
        BoolQueryBuilder boolQuery = new BoolQueryBuilder();
        formatQ(boolQuery, param, dataCode, "wildcard");
        //总条数
        Integer sum = SystemStatic.crud.queryQBuilderAllByMes(SystemStatic.AUDIO_ES_INDEX, SystemStatic.AUDIO_ES_TYPE, boolQuery);
        if (sum == 0) {
            return HtmlResult.failure("没有满足条件的数据");
        }
        long now = DateFormatUtil.getDataTime();
        String taskName = callStartTime.split(" ")[0] + "_" + callEndTime.split(" ")[0] + "_5G数据导出";
        BusVoiceDetails detail = new BusVoiceDetails();
        User userInfo = (User) SecurityUtils.getSubject().getSession().getAttribute(Constants.SESSION_USER_INFO);
        detail.setDatacode(dataCode);
        detail.setName(taskName);
        detail.setCreatetime(new Date());
        detail.setCreateuserid(userInfo.getId());
        detail.setCreateuser(userInfo.getUsername());
        detail.setStatus(BaseEntity.VOICE_EXPORT_BEGIN);
        detail.setFilename(userInfo.getId() + "_" + now + ".xls");
        detail.setFilepath(SystemStatic.SYSTEM_USER_PATH_EXPORT + detail.getFilename());
        detail.setIsdel(BaseEntity.FILE_IS_DEL_NO);
        detail.setLocalip(IPUtil.getLocalIP());
        detail.setParams(param);
        SysArea area = areaService.selectAreaByUserId(userInfo.getId());
        if (null != area) {
            detail.setProvincecode(area.getCode());
            detail.setProvince(area.getArea());
        }
        if (detailsMapper.insertSelective(detail) == 0) {
            return HtmlResult.failure("添加失败");
        }
        //将任务放到阻塞队列中
        ThreadExecutorUtil.executeTask(new Producer(SystemStatic.queue, detail));
        return HtmlResult.success("添加成功");
    }

    @Override
    public HtmlResult selectByExample(BusVoiceDetailsSearch search) {
        BusVoiceDetailsExample example = new BusVoiceDetailsExample();
        BusVoiceDetailsExample.Criteria criteria = example.createCriteria();
        if (!Strings.isNullOrEmpty(search.getName())) {
            criteria.andNameEqualTo(search.getName());
        }
        if (!Strings.isNullOrEmpty(search.getProvincecode())) {
            criteria.andProvincecodeEqualTo(search.getProvincecode());
        }
        if (!Strings.isNullOrEmpty(search.getCreatetimeBegin())) {
            criteria.andCreatetimeGreaterThanOrEqualTo(DateFormatUtil.stringToDate(search.getCreatetimeBegin(), DateFormatUtil.yyyy_MM_dd_HH_mm_ss));
        }
        if (!Strings.isNullOrEmpty(search.getCreatetimeEnd())) {
            criteria.andCreatetimeLessThanOrEqualTo(DateFormatUtil.stringToDate(search.getCreatetimeEnd(), DateFormatUtil.yyyy_MM_dd_HH_mm_ss));
        }
        if (search.getCreateuserid() != null) {
            criteria.andCreateuseridEqualTo(search.getCreateuserid());
        } else {
            // 获取当前登录用户信息
            User userInfo = (User) SecurityUtils.getSubject().getSession().getAttribute(Constants.SESSION_USER_INFO);
            criteria.andCreateuseridEqualTo(userInfo.getId());
        }
        if (search.getStatus() != null) {
            criteria.andStatusEqualTo(search.getStatus());
        }
        criteria.andDatacodeEqualTo(search.getDatacode());
        example.setOrderByClause("createTime desc");
        Page<BusVoiceDetails> page = new Page<>(search.getPageNum() + 1, search.getPageSize());
        List<BusVoiceDetails> detailsList = detailsMapper.selectByExample(page, example);
        for (BusVoiceDetails details : detailsList) {
            if (details.getFinishtime() != null) {
                int textMaxHour = Integer.parseInt(sysConfigService.sysConfigGetByPK(Constants.TEXT_MAX_HOUR).getValue());
                Date invalidTime = DateFormatUtil.addMinute(details.getFinishtime(), (textMaxHour * 60));
                details.setInvalidtime(invalidTime);
            }
        }
        return HtmlResult.success(detailsList, page.getTotalCount());
    }

    @Override
    public List<BusVoiceDetails> selectByExample(BusVoiceDetailsExample example) {
        return detailsMapper.selectByExample(example);
    }

    @Override
    public HtmlResult updateByPrimaryKeySelective(BusVoiceDetails record) {
        record.setLocalip(IPUtil.getLocalIP());
        if (detailsMapper.updateByPrimaryKeySelective(record) == 0) {
            return HtmlResult.failure("修改失败");
        }
        return HtmlResult.success("修改成功");
    }

    @Override
    public HtmlResult updateByPrimaryKey(BusVoiceDetails record) {
        record.setLocalip(IPUtil.getLocalIP());
        if (detailsMapper.updateByPrimaryKey(record) == 0) {
            return HtmlResult.failure("修改失败");
        }
        return HtmlResult.success("修改成功");
    }

    @Override
    public HtmlResult deleteByPrimaryKey(String ids) {
        for (String id : ids.split(",")) {
            if (detailsMapper.deleteByPrimaryKey(Long.parseLong(id)) == 0) {
                return HtmlResult.failure("删除失败");
            }
        }
        return HtmlResult.success("删除成功");
    }

    @Override
    public BusVoiceDetails selectByPrimaryKey(Long id) {
        return detailsMapper.selectByPrimaryKey(id);
    }

    private void formatQ(BoolQueryBuilder boolQuery, JsonBeanPamar param, String method, String wildcard) {
        //sysTags
        if (!Strings.isNullOrEmpty(param.getSystags())) {
            BoolQueryBuilder builder = new BoolQueryBuilder();
            for (String sysTag : param.getSystags().split(",")) {
                builder.should(
                        QueryBuilders.nestedQuery(
                                "callTags",
                                QueryBuilders.boolQuery()
                                        .must(QueryBuilders.wildcardQuery("callTags.tagName", sysTag + "*"))
                                        .mustNot(QueryBuilders.matchQuery("callTags.proType", BaseEntity.PROTYPE_ZX))
                                        .must(QueryBuilders.matchQuery("callTags.proValue", BaseEntity.PROVALUE_YES)),
                                ScoreMode.None
                        )
                );
            }
            boolQuery.must(builder);
        }
        if (param.getCustomersign() != null) {
            boolQuery.must(QueryBuilders.matchQuery("voiceInfo.customerSign", param.getCustomersign()));
        }
        if (!Strings.isNullOrEmpty(param.getCustomerlevels())) {
            BoolQueryBuilder shuldQ = new BoolQueryBuilder();
            for (String customerLevel : param.getCustomerlevels().split(",")) {
                shuldQ.should(QueryBuilders.matchQuery("voiceInfo.customerLevel", customerLevel));
            }
            boolQuery.must(shuldQ);
        }
        if (!Strings.isNullOrEmpty(param.getGroupid())) {
            BoolQueryBuilder shuldQ = new BoolQueryBuilder();
            for (String seatGroup : param.getGroupid().split(",")) {
                shuldQ.should(QueryBuilders.matchQuery("voiceInfo.groupId", seatGroup));
            }
            boolQuery.must(shuldQ);
        }
        if (!Strings.isNullOrEmpty(param.getSatisfaction())) {
            BoolQueryBuilder shuldQ = new BoolQueryBuilder();
            for (String satisfaction : param.getSatisfaction().split(",")) {
                shuldQ.should(QueryBuilders.matchQuery("voiceInfo.satisfaction", satisfaction));
            }
            boolQuery.must(shuldQ);
        }
        if (!Strings.isNullOrEmpty(param.getSeatid())) {
            BoolQueryBuilder shuldQ = new BoolQueryBuilder();
            for (String seatId : param.getSeatid().split(",")) {
                shuldQ.should(QueryBuilders.matchQuery("voiceInfo.seatId", seatId));
            }
            boolQuery.must(shuldQ);
        }
        if (!Strings.isNullOrEmpty(param.getCustomernum())) {
            BoolQueryBuilder shuldQ = new BoolQueryBuilder();
            for (String customerNum : param.getCustomernum().split(",")) {
                shuldQ.should(QueryBuilders.wildcardQuery("voiceInfo.customerNum", "*" + customerNum + "*"));
            }
            boolQuery.must(shuldQ);
        }
        if (!Strings.isNullOrEmpty(param.getCallstarttime())) {
            PubMethod.formatTime(boolQuery, param.getCallstarttime(), param.getCallendtime());
        }
        if (param.getCalldirection() != null) {
            boolQuery.must(QueryBuilders.matchQuery("voiceInfo.callDirection", param.getCalldirection()));
        }
        if (param.getRecallflag() != null) {
            boolQuery.must(QueryBuilders.matchQuery("voiceInfo.reCallFlag", param.getRecallflag()));
        }
        if (!Strings.isNullOrEmpty(param.getOnhook())) {
            PubMethod.formatOnHook(boolQuery, Arrays.asList(param.getOnhook().split(",")));
        }
        if (!Strings.isNullOrEmpty(param.getScene())) {
            BoolQueryBuilder sceneQ = new BoolQueryBuilder();
            for (String scene : param.getScene().split(",")) {
                if ("wildcard".equals(wildcard) || "voiceExport".equals(method)) {
                    sceneQ.should(
                            QueryBuilders.nestedQuery(
                                    "appeals",
                                    QueryBuilders.wildcardQuery("appeals.scene", scene + "*"),
                                    ScoreMode.None
                            )
                    );
                } else if ("5GExport".equals(method) && !"wildcard".equals(wildcard)) {
                    sceneQ.should(
                            QueryBuilders.nestedQuery(
                                    "appeals",
                                    sceneQ.should(QueryBuilders.matchQuery("appeals.scene", scene)),
                                    ScoreMode.None
                            )
                    );
                }
            }
            boolQuery.must(sceneQ);
        }
        if (!Strings.isNullOrEmpty(param.getProvincecode())) {
            if ("voiceExport".equals(method)) {
                boolQuery.must(QueryBuilders.matchQuery("voiceInfo.provinceCode", param.getProvincecode()));
            } else if ("5GExport".equals(method)) {
                PubMethod.formatPro(param.getProvincecode(), boolQuery);
            }
        }
        if ("voiceExport".equals(method)) {
            //callTags
            if (!Strings.isNullOrEmpty(param.getCalltags())) {
                BoolQueryBuilder builder = new BoolQueryBuilder();
                for (String callTag : param.getCalltags().split(",")) {
                    builder.should(
                            QueryBuilders.nestedQuery(
                                    "callTags",
                                    QueryBuilders.boolQuery()
                                            .must(QueryBuilders.wildcardQuery("callTags.tagName", callTag + "*"))
                                            .must(QueryBuilders.matchQuery("callTags.proType", BaseEntity.PROTYPE_ZX)),
                                    ScoreMode.None
                            )
                    );
                }
                boolQuery.must(builder);
            }
        } else if ("5GExport".equals(method)) {
            //callTags
            if (!Strings.isNullOrEmpty(param.getCalltags())) {
                String tagName = "*5G*";
                if (param.getCalltags().contains("携号转网")) {
                    tagName = "*携号转网*";
                }
                boolQuery.must(QueryBuilders.nestedQuery(
                        "callTags",
                        QueryBuilders.boolQuery()
                                .must(QueryBuilders.wildcardQuery("callTags.tagName", tagName))
                                .must(QueryBuilders.matchQuery("callTags.proType", BaseEntity.PROTYPE_ZX)),
                        ScoreMode.None
                ));
            }
        }
    }

    @Override
    public boolean exportVoiceXlsPage(BusVoiceDetails detail) {
        Date date = DateFormatUtil.getAppointDayOfEndTime(new Date());
        try {
            String logTxt = "【处理话单明细导出Impl】";
            detail.setStatus(BaseEntity.VOICE_EXPORT_ING);
            detailsMapper.deleteByPrimaryKey(detail.getId());
            if (detailsMapper.insertSelective(detail) == 0) {
                logger.info(logTxt + "修改任务【" + detail.getName() + "】状态为【处理中】失败,不影响实际功能，任务继续！");
            }
            //根据条件分页查询数据
            BoolQueryBuilder boolQuery = new BoolQueryBuilder();
            formatQ(boolQuery, detail.getParams(), detail.getDatacode(), "wildcard");
            //总条数
            Integer sum = SystemStatic.crud.queryQBuilderAllByMes(SystemStatic.AUDIO_ES_INDEX, SystemStatic.AUDIO_ES_TYPE, boolQuery);
            logger.info(logTxt + "sum = " + sum);
            if (sum == 0) {
                detail.setStatus(BaseEntity.VOICE_EXPORT_NULL);
                detail.setFilepath("");
                detail.setFilename("");
                detail.setFinishtime(date);
                detailsMapper.deleteByPrimaryKey(detail.getId());
                detailsMapper.insertSelective(detail);
                return false;
            }
            //excel一个工作单区存放的最大数据条数
            int excleMax = 50000;
            List<Integer> pageTount = TaskUtil.formatSum(sum, excleMax);//todo最后一个数字需要特殊处理:6022
            //数据
            List<List<String[]>> dataList = new ArrayList<>();
            String[] sheetNames = new String[pageTount.size()];
            String[] includes = new String[]
                    {
                            "serialNumber",
                            "voiceInfo.province",
                            "voiceInfo.callDirection",
                            "voiceInfo.seatGroup",
                            "voiceInfo.seatId",
                            "voiceInfo.customerNum",
                            "voiceInfo.holdDuration",
                            "voiceInfo.callStartTime",
                            "voiceInfo.callEndTime",
                            "voiceInfo.onHook",
                            "voiceInfo.reCallFlag",
                            "voiceInfo.satisfaction",
                            "voiceInfo.customerLevel",
                            "voiceInfo.customerSign",
                            "callTags",
                            "appeals",
                    };
            //分页查询数据
            for (int i = 0; i < pageTount.size(); i++) {
                List<Source> sourceList = new ArrayList<>();
                int excleMax1 = ConfigHelper.getInt("export.data.sheet.max.number", 1000);
                List<Integer> pageTount1 = TaskUtil.formatSum(excleMax, excleMax1);//todo最后一个数字需要特殊处理:6022
                for (Integer aPageTount1 : pageTount1) {
                    List<String> docList = SystemStatic.crud.queryMapQBuilderRetRuleAndWork(
                            SystemStatic.AUDIO_ES_INDEX,
                            SystemStatic.AUDIO_ES_TYPE,
                            aPageTount1,
                            excleMax1,
                            boolQuery,
                            "",
                            "",
                            includes
                    );
                    if (docList.isEmpty()) continue;
                    sourceList.addAll(esUtil.strs2Entitys(docList));
                }
                //生成xls
                formatDataList(dataList, sourceList);
                sheetNames[i] = "Excle" + (i + 1);
            }
            logger.info(logTxt + "生成【" + dataList.size() + "】个sheet");
            LocalExcelUtils noResponseExcelUtils = LocalExcelUtils.initialization();
            noResponseExcelUtils.setDataLists(dataList);
            noResponseExcelUtils.setSheetName(sheetNames);
            if (!FileUtil.createDirectory(detail.getFilepath())) {
                detail.setStatus(BaseEntity.VOICE_EXPORT_NULL);
                detail.setFilepath(null);
                detail.setFinishtime(date);
                detail.setFilename(null);
                detailsMapper.deleteByPrimaryKey(detail.getId());
                detailsMapper.insertSelective(detail);
                return false;
            }
            noResponseExcelUtils.setFilePath(detail.getFilepath());
            noResponseExcelUtils.localNoStyleNoResponse();
            //将文件上传ftp
            //登陆ftp
            FtpUtil ftpUtil = pubMet.getFtpUtil();
            File localFile = new File(detail.getFilepath());
            //创建ftp路径
            if (!ftpUtil.createDirs(SystemStatic.FTP_PATH_EXPORT_VOICE)) {
                FileUtil.deleteFile(localFile);
                throw new RuntimeException();
            }
            //文件上传ftp
            if (!ftpUtil.upload(localFile, SystemStatic.FTP_PATH_EXPORT_VOICE)) {
                throw new RuntimeException();
            }
            //关闭ftp链接
            ftpUtil.disconnect();
            //ftp上传成功，删除本地文件
            FileUtil.deleteFile(localFile);
            //文件路径修改为ftp路径
            detail.setFilepath(SystemStatic.FTP_PATH_EXPORT_VOICE + detail.getFilename());
            detail.setFinishtime(date);
            detail.setStatus(BaseEntity.VOICE_EXPORT_YES);
            detailsMapper.deleteByPrimaryKey(detail.getId());
            detailsMapper.insertSelective(detail);
            return true;
        } catch (Exception e) {
            detail.setStatus(BaseEntity.VOICE_EXPORT_NULL);
            detail.setFinishtime(date);
            detail.setFilepath(null);
            detail.setFilename(null);
            detailsMapper.deleteByPrimaryKey(detail.getId());
            detailsMapper.insertSelective(detail);
            return false;
        }
    }

    private void formatDataList(List<List<String[]>> dataList, List<Source> sourceList) {
        List<String[]> stringList = new ArrayList<>();
        String[] valueString;
        String[] headers = {"编号", "省份", "呼叫类型", "录音流水", "坐席班组", "坐席工号", "客户号码", "通话时长(秒)", "通话开始时间",
                "通话结束时间", "挂机方", "是否重复来电", "满意度", "业务场景", "用户星级", "是否重要客户", "系统标签", "话务小结"};
        stringList.add(headers);
        for (int i = 0; i < sourceList.size(); i++) {
            VoiceInfo info = sourceList.get(i).getVoiceInfo();
            String customerSign = "";
            if (!Strings.isNullOrEmpty(sourceList.get(i).getVoiceInfo().getCustomerSign())) {
                customerSign = sourceList.get(i).getVoiceInfo().getCustomerSign().equals("0") ? "否" : "是";
            }
            StringBuilder busTags = new StringBuilder();//话务小结
            StringBuilder sysTags = new StringBuilder();//系统标签
            if (sourceList.get(i).getCallTags() != null && !sourceList.get(i).getCallTags().isEmpty()) {
                for (BusinessTags tag : sourceList.get(i).getCallTags()) {
                    if (tag.getProType() == BaseEntity.PROTYPE_ZX) {//标签分类 0坐席[话务小结]
                        busTags.append(tag.getTagName()).append("\r\n");
                    } else {
                        if (tag.getProValue() == BaseEntity.PROVALUE_YES) {//系统标签，正确的追加
                            sysTags.append(tag.getTagName()).append("\r\n");
                        }
                    }
                }
            }
            StringBuilder scenes = new StringBuilder();//业务场景
            if (sourceList.get(i).getAppeals() != null && !sourceList.get(i).getAppeals().isEmpty()) {
                for (Appeal appeal : sourceList.get(i).getAppeals()) {
                    scenes.append(appeal.getScene()).append(",");
                }
            }
            valueString = new String[]{
                    (i + 1) + "",
                    info.getProvince(),
                    info.getCallDirection() == 0 ? "呼入" : "呼出",
                    sourceList.get(i).getSerialNumber(),
                    info.getSeatGroup(),
                    info.getSeatId(),
                    info.getCustomerNum(),
                    info.getHoldDuration() + "",
                    info.getCallStartTime(),
                    info.getCallEndTime(),
                    esUtil.changeOnHook(info.getOnHook()),
                    info.getReCallFlag() == 0 ? "否" : "是",
                    esUtil.changeSatisfaction(info.getSatisfaction()),
                    scenes.toString(),
                    esUtil.changeCustomerLevel(info.getCustomerLevel()),
                    customerSign,
                    sysTags.toString(),
                    busTags.toString()
            };
            stringList.add(valueString);
        }
        //sheet
        dataList.add(stringList);
    }

    @Override
    public boolean export5GXlsPage(BusVoiceDetails detail) {
        Date date = DateFormatUtil.getAppointDayOfEndTime(new Date());
        try {
            String logTxt = "【5G城市数据导出Impl】";
            detail.setStatus(BaseEntity.VOICE_EXPORT_ING);
            detailsMapper.deleteByPrimaryKey(detail.getId());
            if (detailsMapper.insertSelective(detail) == 0) {
                logger.info(logTxt + "修改任务【" + detail.getName() + "】状态为【处理中】失败,不影响实际功能，任务继续！");
            }
            Map<String, Map<String, String>> provinceCodes = new HashMap<>();
            Map<String,Object> provinceCityMap = SystemStatic.PROVINCE_CITY_MAP;
            Map<String, Map<String, String>> citys = (Map<String, Map<String, String>>) provinceCityMap.get("5G-ALL");
            //页面获取
            Map<String, List<String>> citysHtml = PubMethod.strToJson(detail.getParams().getProvincecode());
            for (String key : citysHtml.keySet()) {
                Map<String, String> city1 = new HashMap<>();
                for (String code : citysHtml.get(key)) {
                    city1.put(code, citys.get(key).get(code));
                }
                provinceCodes.put(key, city1);
            }
            JsonBeanPamar param = detail.getParams();
            //根据条件分页查询数据
            BoolQueryBuilder boolQuery = new BoolQueryBuilder();
            formatQ(boolQuery, param, detail.getDatacode(), "wildcard");
            //总条数
            Integer sum = SystemStatic.crud.queryQBuilderAllByMes(SystemStatic.AUDIO_ES_INDEX, SystemStatic.AUDIO_ES_TYPE, boolQuery);
            logger.info(logTxt + "sum = " + sum);
            if (sum == 0) {
                detail.setStatus(BaseEntity.VOICE_EXPORT_NULL);
                detail.setFilename(null);
                detail.setFinishtime(date);
                detail.setFilepath(null);
                detailsMapper.deleteByPrimaryKey(detail.getId());
                detailsMapper.insertSelective(detail);
                return false;
            }
            //数据
            List<List<String[]>> dataList = new ArrayList<>();
            String[] sheetNames = new String[2];
            //todo 5G业务趋势-分城市  sheet1
            List<List<String>> sheet1Date = getSheet1Date(logTxt, param, provinceCodes);
            List<String[]> stringList1 = createSheet1(sheet1Date, provinceCodes);
            dataList.add(stringList1);
            sheetNames[0] = "5G业务趋势-分城市";
            //todo 5G业务场景概览  sheet2
            List<List<String>> sheet2Date = getSheet2Date(logTxt, param, provinceCodes);
            if (sheet2Date.isEmpty()) {
                logger.info(logTxt + "业务场景获取失败");
                if (!FileUtil.createDirectory(detail.getFilepath())) {
                    detail.setFilepath(null);
                    detail.setStatus(BaseEntity.VOICE_EXPORT_NULL);
                    detail.setFinishtime(date);
                    detail.setFilename(null);
                    detailsMapper.deleteByPrimaryKey(detail.getId());
                    if (detailsMapper.insertSelective(detail) == 0) {
                        return false;
                    }
                }
            }
            List<String[]> stringList2 = createSheet2(sheet2Date, provinceCodes);
            dataList.add(stringList2);
            sheetNames[1] = "5G业务场景概览";
            //todo 编写xls的表头，组装两个sheet，生成文件
            logger.info(logTxt + "生成【" + dataList.size() + "】个sheet");
            LocalExcelUtils noResponseExcelUtils = LocalExcelUtils.initialization();
            noResponseExcelUtils.setDataLists(dataList);
            noResponseExcelUtils.setSheetName(sheetNames);
            if (!FileUtil.createDirectory(detail.getFilepath())) {
                detail.setStatus(BaseEntity.VOICE_EXPORT_NULL);
                detail.setFilepath(null);
                detail.setFinishtime(date);
                detail.setFilename(null);
                detailsMapper.deleteByPrimaryKey(detail.getId());
                if (detailsMapper.insertSelective(detail) == 0) {
                    return false;
                }
            }
            noResponseExcelUtils.setFilePath(detail.getFilepath());
            noResponseExcelUtils.localNoStyleNoResponse();
            //todo 将文件上传ftp
            //登陆ftp
            logger.info(logTxt + "登陆ftp");
            FtpUtil ftpUtil = pubMet.getFtpUtil();
            File localFile = new File(detail.getFilepath());
            //创建ftp路径
            if (!ftpUtil.createDirs(SystemStatic.FTP_PATH_EXPORT_VOICE)) {
                FileUtil.deleteFile(localFile);
                throw new RuntimeException();
            }
            //文件上传ftp
            if (!ftpUtil.upload(localFile, SystemStatic.FTP_PATH_EXPORT_VOICE)) {
                throw new RuntimeException();
            }
            //关闭ftp链接
            ftpUtil.disconnect();
            //ftp上传成功，删除本地文件
            logger.info(logTxt + "ftp上传成功，删除本地文件");
            FileUtil.deleteFile(localFile);
            //文件路径修改为ftp路径
            detail.setFilepath(SystemStatic.FTP_PATH_EXPORT_VOICE + detail.getFilename());
            detail.setFinishtime(date);
            detail.setStatus(BaseEntity.VOICE_EXPORT_YES);
            detailsMapper.deleteByPrimaryKey(detail.getId());
            detailsMapper.insertSelective(detail);
            return true;
        } catch (Exception e) {
            logger.error(Arrays.toString(e.getStackTrace()));
            detail.setStatus(BaseEntity.VOICE_EXPORT_NULL);
            detail.setFinishtime(date);
            detail.setFilepath(null);
            detail.setFilename(null);
            detailsMapper.deleteByPrimaryKey(detail.getId());
            if (detailsMapper.insertSelective(detail) == 0) {
                return false;
            }
        }
        return true;
    }

    private List<List<String>> getSheet1Date(String logTxt, JsonBeanPamar param, Map<String, Map<String, String>> provinceCodes) throws IOException {
        logTxt = logTxt + "【5G业务趋势-分城市】";
        logger.info(logTxt);
        List<List<String>> mapList = new ArrayList<>();
        List<String> dateList = DateFormatUtil.getBetweenDate(param.getCallstarttime().split(" ")[0], param.getCallendtime().split(" ")[0]);
        //todo 计算所获取业务场景  返回所有的叶子节点
        List<String> sceneList = getSceneList(logTxt, param.getScene(), 0);
        for (String date : dateList) {
            logger.info(logTxt + date);
            logger.info(logTxt + StringUtils.join(provinceCodes, ","));
            List<String> map = new ArrayList<>();
            map.add(date);
            JsonBeanPamar bloBs = new JsonBeanPamar();
            bloBs.setCallstarttime(date + " 00:00:00");
            bloBs.setCallendtime(date + " 23:59:59");
            bloBs.setScene(String.join(",", sceneList));
            String tagName = "*5G*";
            if (param.getCalltags().contains("携号转网")) {
                tagName = "*携号转网*";
            }
            bloBs.setCalltags(tagName);
            bloBs.setProvincecode(param.getProvincecode());
            //话务量
            int voiceAll = 0;
            //非常满意
            int satisFei = 0;
            //满意
            int satisMan = 0;
            //不满意
            int satisBu = 0;
            //未评价
            int satisWei = 0;
            //星级查询
            //一星级
            int levelYi = 0;
            //二星级
            int levelEr = 0;
            //三星级
            int levelSan = 0;
            //四星级
            int levelSi = 0;
            //五星级
            int levelWu = 0;
            //六星级
            int levelLiu = 0;
            //七星级
            int levelQi = 0;
            //无星级
            int levelMei = 0;
            List<Source> sourceList = getNumByQueryDoc(bloBs);
            for (Source source : sourceList) {
                voiceAll ++;
                if ("1".equals(source.getVoiceInfo().getSatisfaction())) {
                    satisFei++;
                }
                if ("2".equals(source.getVoiceInfo().getSatisfaction())) {
                    satisMan++;
                }
                if ("3".equals(source.getVoiceInfo().getSatisfaction())) {
                    satisBu++;
                }
                if ("4".equals(source.getVoiceInfo().getSatisfaction())) {
                    satisWei++;
                }
                if ("3100".equals(source.getVoiceInfo().getCustomerLevel())) {
                    levelYi++;
                }
                if ("3200".equals(source.getVoiceInfo().getCustomerLevel())) {
                    levelEr++;
                }
                if ("3300".equals(source.getVoiceInfo().getCustomerLevel())) {
                    levelSan++;
                }
                if ("3400".equals(source.getVoiceInfo().getCustomerLevel())) {
                    levelSi++;
                }
                if ("3500".equals(source.getVoiceInfo().getCustomerLevel())) {
                    levelWu++;
                }
                if ("3600".equals(source.getVoiceInfo().getCustomerLevel())) {
                    levelLiu++;
                }
                if ("3700".equals(source.getVoiceInfo().getCustomerLevel())) {
                    levelQi++;
                }
                if ("3800".equals(source.getVoiceInfo().getCustomerLevel())) {
                    levelMei++;
                }
            }
            //话务量
            map.add(Integer.toString(voiceAll));
            //非常满意
            map.add(Integer.toString(satisFei));
            //满意
            map.add(Integer.toString(satisMan));
            //不满意
            map.add(Integer.toString(satisBu));
            //未评价
            map.add(Integer.toString(satisWei));
            //满意度
            map.add(CastUtil.formatDec(satisFei + satisMan, satisFei + satisMan + satisBu));
            //星级查询
            //一星级
            map.add(Integer.toString(levelYi));
            //二星级
            map.add(Integer.toString(levelEr));
            //三星级
            map.add(Integer.toString(levelSan));
            //四星级
            map.add(Integer.toString(levelSi));
            //五星级
            map.add(Integer.toString(levelWu));
            //六星级
            map.add(Integer.toString(levelLiu));
            //七星级
            map.add(Integer.toString(levelQi));
            //无星级
            map.add(Integer.toString(levelMei));
            //5G城市分别话务量
            for (Map.Entry<String, Map<String, String>> entry : provinceCodes.entrySet()) {
                //直辖市为空，进行省份查询
                if (!entry.getValue().isEmpty()) {
                    for (Map.Entry<String, String> city : entry.getValue().entrySet()) {
                        int proAll = 0;
                        for (Source source : sourceList) {
                            if (city.getKey().equals(source.getVoiceInfo().getCustomerloc())) {
                                proAll ++;
                            }
                        }
                        map.add(Integer.toString(proAll));
                    }
                } else {
                    int proAll = 0;
                    for (Source source : sourceList) {
                        if (entry.getKey().equals(source.getVoiceInfo().getProvinceCode())) {
                            proAll ++;
                        }
                    }
                    map.add(Integer.toString(proAll));
                }
            }
            mapList.add(map);
        }
        return mapList;
    }

    private List<Source> getNumByQueryDoc(JsonBeanPamar param) {
        BoolQueryBuilder boolQuery = new BoolQueryBuilder();
        formatQ(boolQuery, param, "5GExport", "noWildcard");
        List<Source> docList = new ArrayList<>();
        //总条数
        Integer sum = SystemStatic.crud.queryQBuilderAllByMes(SystemStatic.AUDIO_ES_INDEX, SystemStatic.AUDIO_ES_TYPE, boolQuery);
        //单词查询条数
        int excleMax = ConfigHelper.getInt("export.data.sheet.max.number", 1000);
        List<Integer> pageTout = TaskUtil.formatSum(sum, excleMax);//todo最后一个数字需要特殊处理:6022
        String[] includes = new String[]
                {
                        "serialNumber",
                        "voiceInfo"
                };
        for (Integer aPageTout : pageTout) {
            List<String> docs = SystemStatic.crud.queryMapQBuilderRetRuleAndWork(
                    SystemStatic.AUDIO_ES_INDEX,
                    SystemStatic.AUDIO_ES_TYPE,
                    aPageTout,
                    excleMax,
                    boolQuery,
                    "",
                    "",
                    includes
            );
            if (docs.isEmpty()) continue;
            docList.addAll(esUtil.strs2Entitys(docs));
        }
        return docList;
    }

    private List<String[]> createSheet1(List<List<String>> sheet1Date, Map<String, Map<String, String>> provinceCodes) {
        String[] headerStr = {"序号", "日期", "话务量", "非常满意", "满意", "不满意", "未评价", "满意度", "一星级", "二星级",
                "三星级", "四星级", "五星级", "六星级", "七星级", "零星级"};
        List<String> headerList = Stream.of(headerStr).collect(Collectors.toList());
        for (Map.Entry<String, Map<String, String>> entry : provinceCodes.entrySet()) {
            if (!entry.getValue().isEmpty()) {
                for (Map.Entry<String, String> city : entry.getValue().entrySet()) {
                    headerList.add(city.getValue());
                }
            } else {
                String pro = areaService.selectByPrimaryCode(entry.getKey()).getArea();
                headerList.add(pro);
            }
        }
        String[] headers = headerList.toArray(new String[0]);
        List<String[]> stringList = new ArrayList<>();
        String[] valueString;
        stringList.add(headers);
        for (int i = 0; i < sheet1Date.size(); i++) {
            valueString = new String[sheet1Date.get(i).size() + 1];
            valueString[0] = (i+1) + "";
            int j = 1;
            for (String value : sheet1Date.get(i)) {
                valueString[j] = value;
                j++;
            }
            stringList.add(valueString);
        }
        return stringList;
    }

    //获取业务场景下的所有叶子节点
    private List<String> getSecens(List<String> sceneListAll, String scene) {
        List<String> sceneList = new ArrayList<>();
        for (String sc : sceneListAll) {
            if (sc.contains(scene)) {
                sceneList.add(sc);
            }
        }
        return sceneList;
    }

    private List<List<String>> getSheet2Date(String logTxt, JsonBeanPamar param, Map<String, Map<String, String>> provinceCodes) throws IOException {
        logTxt = logTxt + "【5G业务场景概览】";
        logger.info(logTxt);
        List<List<String>> mapList = new ArrayList<>();
        //todo 计算所获取业务分类及场景  flag为0/null，返回所有的叶子节点 flag不为0时返回叶子节点及所有分类
        List<String> sceneListAll = getSceneList(logTxt, param.getScene(), 1);
        List<String> sceneList = getSceneList(logTxt, param.getScene(), 0);
        logger.info(logTxt + "业务分类及场景sceneListAll.size() = " + sceneListAll.size());
        logger.info(logTxt + "业务分类及场景sceneList.size() = " + sceneList.size());
        if (sceneListAll.isEmpty()) {
            return mapList;
        }
        if (sceneList.isEmpty()) {
            return mapList;
        }
        for (String scene : sceneListAll) {
            if ("专题分析".equals(scene)) continue;
            List<String> map = new ArrayList<>();
            JsonBeanPamar bloBs = new JsonBeanPamar();
            bloBs.setCallstarttime(param.getCallstarttime());
            bloBs.setCallendtime(param.getCallendtime());
            bloBs.setScene(String.join(",", getSecens(sceneList, scene)));
            String tagName = "*5G*";
            if (param.getCalltags().contains("携号转网")) {
                tagName = "*携号转网*";
            }
            bloBs.setCalltags(tagName);
            bloBs.setProvincecode(param.getProvincecode());
            map.add(scene);
            //话务量
            int voiceAll = 0;
            //非常满意
            int satisFei = 0;
            //满意
            int satisMan = 0;
            //不满意
            int satisBu = 0;
            //未评价
            int satisWei = 0;
            //星级查询
            //一星级
            int levelYi = 0;
            //二星级
            int levelEr = 0;
            //三星级
            int levelSan = 0;
            //四星级
            int levelSi = 0;
            //五星级
            int levelWu = 0;
            //六星级
            int levelLiu = 0;
            //七星级
            int levelQi = 0;
            //无星级
            int levelMei = 0;
            List<Source> sourceList = getNumByQueryDoc(bloBs);
            for (Source source : sourceList) {
                voiceAll ++;
                if ("1".equals(source.getVoiceInfo().getSatisfaction())) satisFei++;
                if ("2".equals(source.getVoiceInfo().getSatisfaction())) satisMan++;
                if ("3".equals(source.getVoiceInfo().getSatisfaction())) satisBu++;
                if ("4".equals(source.getVoiceInfo().getSatisfaction())) satisWei++;
                if ("3100".equals(source.getVoiceInfo().getCustomerLevel())) {
                    levelYi++;
                }
                if ("3200".equals(source.getVoiceInfo().getCustomerLevel())) {
                    levelEr++;
                }
                if ("3300".equals(source.getVoiceInfo().getCustomerLevel())) {
                    levelSan++;
                }
                if ("3400".equals(source.getVoiceInfo().getCustomerLevel())) {
                    levelSi++;
                }
                if ("3500".equals(source.getVoiceInfo().getCustomerLevel())) {
                    levelWu++;
                }
                if ("3600".equals(source.getVoiceInfo().getCustomerLevel())) {
                    levelLiu++;
                }
                if ("3700".equals(source.getVoiceInfo().getCustomerLevel())) {
                    levelQi++;
                }
                if ("3800".equals(source.getVoiceInfo().getCustomerLevel())) {
                    levelMei++;
                }
            }
            //话务量
            map.add(Integer.toString(voiceAll));
            //非常满意
            map.add(Integer.toString(satisFei));
            //满意
            map.add(Integer.toString(satisMan));
            //不满意
            map.add(Integer.toString(satisBu));
            //未评价
            map.add(Integer.toString(satisWei));
            //满意度
            map.add(CastUtil.formatDec(
                    satisFei + satisMan,
                    satisFei + satisMan + satisBu)
            );
            //一星级
            map.add(Integer.toString(levelYi));
            //二星级
            map.add(Integer.toString(levelEr));
            //三星级
            map.add(Integer.toString(levelSan));
            //四星级
            map.add(Integer.toString(levelSi));
            //五星级
            map.add(Integer.toString(levelWu));
            //六星级
            map.add(Integer.toString(levelLiu));
            //七星级
            map.add(Integer.toString(levelQi));
            //无星级
            map.add(Integer.toString(levelMei));
            //5G城市分别话务量
            for (Map.Entry<String, Map<String, String>> entry : provinceCodes.entrySet()) {
                if (!entry.getValue().isEmpty()) {
                    for (Map.Entry<String, String> city : entry.getValue().entrySet()) {
                        //话务量
                        int proAll = 0;
                        //非常满意
                        int proSatisFei = 0;
                        //满意
                        int proSatisMan = 0;
                        //不满意
                        int proSatisBu = 0;
                        for (Source source : sourceList) {
                            if (city.getKey().equals(source.getVoiceInfo().getCustomerloc())) {
                                proAll++;
                                if ("1".equals(source.getVoiceInfo().getSatisfaction())) proSatisFei++;
                                if ("2".equals(source.getVoiceInfo().getSatisfaction())) proSatisMan++;
                                if ("3".equals(source.getVoiceInfo().getSatisfaction())) proSatisBu++;
                            }
                        }
                        //话务量
                        map.add(Integer.toString(proAll));
                        //满意度
                        map.add(CastUtil.formatDec(
                                proSatisFei + proSatisMan,
                                proSatisFei + proSatisMan + proSatisBu)
                        );
                    }
                } else {
                    //话务量
                    int proAll = 0;
                    //非常满意
                    int proSatisFei = 0;
                    //满意
                    int proSatisMan = 0;
                    //不满意
                    int proSatisBu = 0;
                    for (Source source : sourceList) {
                        if (entry.getKey().equals(source.getVoiceInfo().getProvinceCode())) {
                            proAll++;
                            if ("1".equals(source.getVoiceInfo().getSatisfaction())) proSatisFei++;
                            if ("2".equals(source.getVoiceInfo().getSatisfaction())) proSatisMan++;
                            if ("3".equals(source.getVoiceInfo().getSatisfaction())) proSatisBu++;
                        }
                    }
                    //话务量
                    map.add(Integer.toString(proAll));
                    //满意度
                    map.add(CastUtil.formatDec(
                            proSatisFei + proSatisMan,
                            proSatisFei + proSatisMan + proSatisBu)
                    );
                }
            }
            mapList.add(map);
        }
        return mapList;
    }

    private List<String> getSceneList(String logTxt, String scene, int flag) throws IOException {
        Map<String, Object> param = new HashMap<>();
        param.put("city", "");
        param.put("flag", flag);//flag为0或者你不传这个字段的时候，返回所有的叶子节点 flag不为0时返回叶子节点及所有分类
        if ("专题分析-5G".equals(scene)) {
            param.put("serviceId", "1902830");//专题分析-5G
        } else if ("专题分析-携号转网".equals(scene)) {
            param.put("serviceId", "1908328");//专题分析-携号转网
        }
        String response = HttpUtil.sendJsonHttpPost(PropertiesUtil.getProperty("sendSceneListDispatch"), JSONObject.fromObject(param).toString());
        logger.info( logTxt + "请求接口返回值" + response);
        JSONObject classify = JSONObject.fromObject(response);
        int rtnCode = classify.getInt("code");
        if (rtnCode == -9999) {
            return new ArrayList<>();
        }
        JSONObject jsonObject = classify.getJSONObject("data");
        List<String> scenes = new ArrayList<>();
        JSONArray jsonArray2 = jsonObject.getJSONArray("scene");
        if (jsonArray2 != null && !jsonArray2.isEmpty()) {
            scenes.addAll(jsonArray2);
        }
        if (scenes.isEmpty()) {
            return new ArrayList<>();
        }
        return scenes;
    }

    private List<String[]> createSheet2(List<List<String>> sheet2Date, Map<String, Map<String, String>> provinceCodes) {
        String[] headerStr = {"序号", "业务分类及场景", "话务量", "非常满意", "满意", "不满意", "未评价", "满意度", "一星级",
                "二星级", "三星级", "四星)", "五星级", "六星级", "七星级", "零星级"};
        List<String> headerList = Stream.of(headerStr).collect(Collectors.toList());
        for (Map.Entry<String, Map<String, String>> entry : provinceCodes.entrySet()) {
            if (!entry.getValue().isEmpty()) {
                for (Map.Entry<String, String> city : entry.getValue().entrySet()) {
                    headerList.add("话务量(" + city.getValue() + ")");
                    headerList.add("满意度(" + city.getValue() + ")");
                }
            } else {
                String pro = areaService.selectByPrimaryCode(entry.getKey()).getArea();
                headerList.add("话务量(" + pro + ")");
                headerList.add("满意度(" + pro + ")");
            }
        }
        String[] headers = headerList.toArray(new String[0]);
        List<String[]> stringList = new ArrayList<>();
        stringList.add(headers);
        for (int i = 0; i < sheet2Date.size(); i++) {
            String[] valueString = new String[sheet2Date.get(i).size() + 1];
            valueString[0] = (i+1) + "";
            int j = 1;
            for (String value : sheet2Date.get(i)) {
                valueString[j] = value;
                j++;
            }
            stringList.add(valueString);
        }
        return stringList;
    }
}
